package com.source.barcodeMachineStatus.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.source.barcodeMachineStatus.entity.BarcodeMachineStatus;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.source.barcodeMachineStatus.vo.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author fengq
 * @since 2021-02-27
 */
@Repository
@Mapper
public interface BarcodeMachineStatusMapper extends BaseMapper<BarcodeMachineStatus> {
    /**
     * getVoListById 获取脚标机绑定的档口信息列表
     * @param barcodeMachineId
     * @return
     */

    String SELECT = "select a.barcodeNumber,a.labelingDate,a.barcodeMachineNumber,d.stallsName ,b.poultryParentName,e.address \n" +
            "from labeling_record as a,poultry_category_parent as b,poultry_category as c,stalls as d,slaughter_house as e \n" +
            "where a.poultryCategoryId=c.poultryCategoryId  \n" +
            "and a.isDelete=0\n " +
            "and b.isDelete=0\n " +
            "and c.isDelete=0\n " +
            "and d.isDelete=0\n " +
            "and e.isDelete=0\n " +
            "and b.poultryCategoryParentId=c.parentId \n" +
            "and d.stallsId=a.stallsId \n" +
            "and e.slaughterHouseId=d.slaughterHouseId \n";

    @Select("select a.barcodeNumber,a.labelingDate,a.barcodeMachineNumber,d.stallsName ,b.poultryParentName,e.address \n" +
            "from labeling_record as a,poultry_category_parent as b,poultry_category as c,stalls as d,slaughter_house as e \n" +
            "where a.poultryCategoryId=c.poultryCategoryId  \n" +
            "and a.isDelete=0\n " +
            "and b.isDelete=0\n " +
            "and c.isDelete=0\n " +
            "and d.isDelete=0\n " +
            "and e.isDelete=0\n " +
            "and b.poultryCategoryParentId=c.parentId \n" +
            "and d.stallsId=a.stallsId \n" +
            "and e.slaughterHouseId=d.slaughterHouseId \n" +
            "and a.barcodeMachineNumber=${barcodeMachineId}\t")
    List<StallsMessageVo> getStallsMessageVoById(@Param("barcodeMachineId") String barcodeMachineId);


    /**
     * getOneBarcodeMachineStatusVo 获取一个脚标机监控展示信息
     * @param id
     * @return
     */
    @Select("select a.id,barcodeMachineNumber,loginDate,state,barcodeSurplus,b.name,offlineTime,b.loginName,b.userId  " +
            "from barcode_machine_status as a ,sys_user as b " +
            "where a.userId= b.userId " +
            "and b.isDelete=0 " +
            "and a.id=${id}")
    BarcodeMachineStatusVo getOneVo(@Param("id") String id);

    /**
     * getPageVo 查询所有的BarcodeMachineStatusVo
     * @param page
     * @return IPage<BarcodeMachineStatusVo>
     */
    @Select("select a.id,barcodeMachineNumber,loginDate,state,barcodeSurplus,b.name,offlineTime,b.loginName,b.userId " +
            "from barcode_machine_status as a ,sys_user as b " +
            "where a.userId= b.userId " +
            "and b.isDelete=0 ")
    IPage<BarcodeMachineStatusVo> getPageVo(Page<BarcodeMachineStatusVo> page);

    /**
     * getAllUserNameAndId  获取用户所有的id和用户名
     * @return List<UserNameVo>
     */
    @Select("select userId,loginName,name from sys_user where isDelete=0 ")
    List<UserNameVo> getAllUserNameAndId();

    /**
     * getLikePage 根据关键词进行模糊查询
     * @param page
     * @param keyword
     * @return
     */
    @Select("select distinct a.id,barcodeMachineNumber,loginDate,state,barcodeSurplus,b.name,offlineTime,b.loginName,b.userId \n" +
            "from barcode_machine_status as a ,sys_user as b " +
            "where a.userId= b.userId " +
            "and b.isDelete=0 " +
            "and (name like \"%${keyword}%\" " +
            " or barcodeMachineNumber like \"%${keyword}%\" " +
            " or loginDate like \"%${keyword}%\" " +
            " or state like \"%${keyword}%\" " +
            " or barcodeSurplus like \"%${keyword}%\" " +
            " or offlineTime like \"%${keyword}%\" " +
            " )")
    IPage<BarcodeMachineStatusVo> getLikePageVo(Page<BarcodeMachineStatusVo> page,@Param("keyword") String keyword);

    /**
     * selectCountBarcodeMachineByStatus
     * @param status
     * @param houseId
     * @return
     */
    @Select({"<script> select Count(*) \n" +
            "from barcode_machine_status as a,house_machine_grant as b\n" +
            "where b.isDelete=0\n" +
            "and b.machineNumber=a.barcodeMachineNumber\n" +
            "and b.houseId='${houseId}' \n" +
            "<if test=\"status!=null and status!='' \" > " +
            "and a.state='${status}'</if>" +
            "</script> "})
    long selectCountBarcodeMachineByStatus(@Param("status") String status,@Param("houseId") String houseId);

    /**
     * selectBarcodeMachineAddressByHouseId 根据屠宰场id查询角标位置分布
     * @param houseId
     * @param state
     * @return
     */
    @Results(value = {
            @Result(property = "slaughterHouseId",column = "slaughterHouseId"),
            @Result(property = "slaughterHouseName",column = "slaughterHouseName"),
            @Result(property = "barcodeMachineNumber",column = "barcodeMachineNumber"),
            @Result(property = "centerVo",column = "barcodeMachineNumber",javaType = CenterVo.class,one = @One(select = "com.source.barcodeMachineStatus.mapper.BarcodeMachineStatusMapper.selectCenterByNumber"))
    })
    @Select({"<script>select sh.slaughterHouseId,slaughterHouseName,hmg.machineNumber as barcodeMachineNumber,s.stallsName\n" +
            "            from slaughter_house as sh,barcode_machine_status as bms,house_machine_grant as hmg,stalls as s,barcode_machine_grant as bmg\n" +
            "            where sh.isDelete=0\n" +
            "            and hmg.isDelete=0\n" +
            "\t\t\t\t\t\tand bmg.isDelete=0\n" +
            "\t\t\t\t\t\tand s.isDelete=0\n" +
            "\t\t\t\t\t\tand bmg.stallsId=s.stallsId\n" +
            "\t\t\t\t\t\tand hmg.stallsGrantId=bmg.grantId\n" +
            "            and sh.slaughterHouseId=hmg.houseId\n" +
            "            and bms.barcodeMachineNumber=hmg.machineNumber\n" +
            "and sh.slaughterHouseId='${houseId}'" +
            "<if test=\" state!=null and state!='' and state!='state'\"> and bms.state='${state}' </if>" +
            " </script>"})
    List<BarcodeMachineAddressVo> selectBarcodeMachineAddressByHouseId(@Param("houseId") String houseId,@Param("state") String state);

    /**
     * selectCenterByNumber
     * @param barcodeMachineNumber
     * @return
     */
    @Select("select latitude as lat,longitude as lng\n" +
            "from barcode_machine\n" +
            "where isDelete=0\n" +
            "and barcodeMachineNumber='${barcodeMachineNumber}'")
    CenterVo selectCenterByNumber(@Param("barcodeMachineNumber") String barcodeMachineNumber);

    @Select("SELECT stallsName,b.state,b.barcodeMachineNumber,b.barcodeSurplus\n" +
            "from stalls as a ,barcode_machine_status as b,house_machine_grant as c\n" +
            "where a.stallsId=c.stallsGrantId\n" +
            "and  b.barcodeMachineNumber=c.machineNumber\n" +
            "and a.isDelete=0\n" +
            "and c.isDelete=0  " +
            "and c.houseId='${houseId}' " +
            "and b.state ='${status/}' ")
    IPage<BarcodeCountChart>selectBarcodeCountAndStallNameAndStatus(Page<BarcodeCountChart> page,@Param("houseId")String houseId,@Param("status") String status);



    @Results(value = {
            @Result(property = "slaughterHouseId",column = "slaughterHouseId"),
            @Result(property = "slaughterHouseName",column = "slaughterHouseName"),
            @Result(property = "barcodeMachineNumber",column = "barcodeMachineNumber"),
            @Result(property = "centerVo",column = "barcodeMachineNumber",javaType = CenterVo.class,one = @One(select = "com.source.barcodeMachineStatus.mapper.BarcodeMachineStatusMapper.selectCenterByNumber"))
    })
    @Select({"select sh.slaughterHouseId,slaughterHouseName,hmg.machineNumber as barcodeMachineNumber,s.stallsName\n" +
            "            from slaughter_house as sh,barcode_machine_status as bms,house_machine_grant as hmg,stalls as s,barcode_machine_grant as bmg\n" +
            "            where sh.isDelete=0\n" +
            "            and hmg.isDelete=0\n" +
            "\t\t\t\t\t\tand bmg.isDelete=0\n" +
            "\t\t\t\t\t\tand s.isDelete=0\n" +
            "\t\t\t\t\t\tand bmg.stallsId=s.stallsId\n" +
            "\t\t\t\t\t\tand hmg.stallsGrantId=bmg.grantId\n" +
            "            and sh.slaughterHouseId=hmg.houseId\n" +
            "            and bms.barcodeMachineNumber=hmg.machineNumber\n" +
            "and sh.slaughterHouseId='${houseId}'" +
            "and concat(slaughterHouseName,machineNumber,stallsName) like '%${keyword}%' "})
    List<BarcodeMachineAddressVo> selectBarcodeMachineAddressByHouseIdAndKeyword(@Param("houseId") String houseId,@Param("keyword")String keyword);

}
